import { app, shell, BrowserWindow, ipcMain } from 'electron'
import { join, resolve } from 'path'
import { electronApp, optimizer, is } from '@electron-toolkit/utils'
import icon from '../../resources/icon.png?asset'
const { createDataTable, db } = require('../../resources/db/sqlite3db')
// const fs = require('fs')
let mainWindow = null
function createWindow() {
  // Create the browser window.
  createDataTable()
  mainWindow = new BrowserWindow({
    width: 900,
    height: 670,
    show: false,
    // title: 'Roll Call Machine',
    // autoHideMenuBar: true,
    // titleBarStyle: 'hidden',
    // titleBarOverlay: {
    //   color: 'rgba(223, 223, 223, 0.3)',
    //   symbolColor: 'white'
    // },
    frame: false,
    ...(process.platform === 'linux' ? { icon } : {}),
    webPreferences: {
      preload: join(__dirname, '../preload/index.js'),
      sandbox: false
    }
  })
  //开发者工具
  // mainWindow.webContents.openDevTools()
  mainWindow.setTitle('Roll Call Machine')
  mainWindow.on('ready-to-show', () => {
    mainWindow.show()
  })
  mainWindow.webContents.setWindowOpenHandler((details) => {
    shell.openExternal(details.url)
    return { action: 'deny' }
  })

  // HMR for renderer base on electron-vite cli.
  // Load the remote URL for development or the local html file for production.
  if (is.dev && process.env['ELECTRON_RENDERER_URL']) {
    mainWindow.loadURL(process.env['ELECTRON_RENDERER_URL'])
  } else {
    mainWindow.loadFile(join(__dirname, '../renderer/index.html'))
  }
}

// This method will be called when Electron has finished
// initialization and is ready to create browser windows.
// Some APIs can only be used after this event occurs.
// 分割数组
function chunkArray(array, size) {
  const result = []
  for (let i = 0; i < array.length; i += size) {
    const chunk = array.slice(i, i + size)
    result.push(chunk)
  }
  return result
} /**
 *
   根据class分组并统计记录数
 SELECT class,
          SUM( CASE  WHEN res=1 THEN 1 ELSE 0 END) AS g,
          SUM( CASE  WHEN res=2 THEN 1 ELSE 0 END) AS h,
          SUM( CASE  WHEN res=3 THEN 1 ELSE 0 END) AS e
          FROM callDeatils
          GROUP BY class
          ORDER BY class 
 */
class Result {
  constructor(data1, success = true) {
    this.data = {
      data: data1,
      success
    }
  }
}
//数据库操作函数
const dbPromise = (method, ...args) => {
  return new Promise((resolve, reject) => {
    db[method](...args, (err, result) => {
      if (err) {
        reject(err)
      } else {
        resolve(result)
      }
    })
  })
}
//对User表操作
async function DataByUser(option, obj) {
  let sql
  let date = new Date();
  let year = date.getFullYear();
  console.log(year);
  let m = date.getMonth() + 1;
  let day = date.getDate();
  /**
   * 分页查询
   * 查询总记录数
   * 查询学生信息 学生基本信息 头像 问题回答记录
   *
   */
  if (option == 'page') {
    //通过学号和姓名查找数据并分页
    let { page, size, num, name, classList, isRandom } = obj
    let strClassList = classList?.map((e) => `'${e}'`).join(',')
    let comSql = `
     ${name || num || classList != null ? 'WHERE' : ''}  
    ${name ? `user.name LIKE '%${name}%'` : ''} 
    ${name && num ? 'or' : ''}  
    ${num ? `user.num LIKE '%${num}%'` : ''}
    ${name && num && classList != null ? 'and' : ''}  
    ${classList != null ? `user.class in (${strClassList})` : ''}
    `
    sql = `SELECT  COUNT(*) AS total FROM user ${comSql}`
    let total = await dbPromise('all', sql).then((res) => {
      return res
    })
    console.log(` ${isRandom ? 'ORDER BY RANDOM()' : 'ORDER BY LENGTH(user.num),user.num '}`)
    sql = `SELECT user.*,fileData.pic as pic
           FROM user LEFT JOIN fileData ON user.num = fileData.num ${comSql}
          ${isRandom ? 'ORDER BY RANDOM()' : 'ORDER BY LENGTH(user.num),user.num '}
           LIMIT ${size} OFFSET ${(page - 1) * size} 
        `
    return await dbPromise('all', sql)
      .then(async (userList) => {
        for (const e of userList) {
          sql = `SELECT *
           FROM  callDetails WHERE num = '${e.num}' ORDER BY callTime`
          let res = await dbPromise('all', sql)
          e.call = res
        }
        return new Result({ list: userList, total: total[0]['total'] })
      })
      .catch((err) => {
        console.log(err)
        return new Result(null, false)
      })
  }
  //成功返回sucess
  if (option == 'insert') {
    try {
      let chunks = chunkArray(obj, 100)
      chunks.forEach(async (item) => {
        let place = item.map(() => '(?,?,?,?)').join(', ')
        let values = item.flatMap((user) => {
          if (user) {
            return [user.name || '', user.num || '', user.sex || '', user.class || '']
          }
        })
        sql = `INSERT OR IGNORE INTO user(name,num,sex,class) values${place}
        ON CONFLICT (num) DO UPDATE SET name = excluded.name,
        sex=excluded.sex,class=excluded.class,name=excluded.name;
        `
        await dbPromise('run', sql, values)
      })
      sql = 'SELECT DISTINCT  class From user'
      let res = await dbPromise('all', sql)
      res = res.map((e) => e.class)
      res.sort()
      return new Result({ list: res })
    } catch {
      return new Result(null, false)
    }
  }
  //未更改
  if (option == 'updataImg') {
    let chunks = chunkArray(obj, 10)
    chunks.forEach(async (item) => {
      console.log(item)
      if (item.length > 0) {
        sql = 'UPDATE user SET pic =CASE num '
        item.forEach((e) => {
          sql += `WHEN '${e.num}' THEN '${e.pic}'`
        })
        sql += 'END '
        sql += `WHERE num IN (${item.map((e) => e.num).join(',')})`
        await dbPromise('run', sql)
          .then((res) => {
            console.log('success', res)
            return new Result(null)
          })
          .catch((err) => {
            console.log(err)
            return new Result(null, false)
          })
      }
    })
  }
  if (option == 'insertImage') {
    //新增或者更新头像
    console.log('begin')
    let chunks = chunkArray(obj, 100)
    chunks.forEach(async (item) => {
      let place = item.map(() => '(?,?)').join(', ')
      let values = item.flatMap((user) => [user.num || '', user.pic || ''])
      sql = `INSERT OR IGNORE INTO fileData(num,pic) values${place}
        ON CONFLICT (num) DO UPDATE SET pic = excluded.pic;
        `
      await dbPromise('run', sql, values)
    })
    console.log('end')
    return new Result(null)
  }
  if (option == 'delete') {
    let str = obj.map((e) => `'${e}'`).join(',')
    //删除用户
    try {
      sql = `DELETE FROM user WHERE num in (${str})`
      await dbPromise('run', sql)
      sql = `DELETE FROM fileData WHERE num in (${str})`
      await dbPromise('run', sql)
      return new Result(null)
    } catch (err) {
      return new Result(null, false)
    }
  }
  //查询班级信息 包含总人数 男女生
  if (option == 'getCalss') {
    try {

      let comSql = `
      SUM(CASE WHEN answerResult='1' THEN 1 ELSE 0 END) as correct,
      SUM(CASE WHEN answerResult='2' THEN 1 ELSE 0 END) as basicallyCorrect,
      SUM(CASE WHEN answerResult='3' THEN 1 ELSE 0 END) as incorrect
      `
      sql = ` 
      SELECT user.class,
      COUNT(*) as userNum,
      SUM(CASE WHEN user.sex='男' THEN 1 ELSE 0 END) as boy,
      SUM(CASE WHEN user.sex='女' THEN 1 ELSE 0 END) as girl
      FROM user
      GROUP BY user.class
      ORDER BY LENGTH(user.class),user.class`
      let info = await dbPromise('all', sql)
      for (const e of info) {
        console.log(e.class);
        sql = `SELECT
        ${comSql}
        FROM  callDetails WHERE class ='${e.class}' AND month='${m}' AND year='${year}'
        `
        let res = await dbPromise('all', sql)
        console.log(sql);
        e.correct = res[0].correct ?? 0
        e.basicallyCorrect = res[0].basicallyCorrect ?? 0
        e.incorrect = res[0].incorrect ?? 0
      }
      for (const e of info) {
        console.log(e.class);
        sql = `SELECT
        month,
        ${comSql}
        FROM  callDetails WHERE class ='${e.class}' AND year='${year}'
        GROUP BY month
        `
        let res = await dbPromise('all', sql)
        e.result = res
        console.log(res)
      }
      console.log(info);

      return new Result({ list: info })
    } catch (err) {
      console.log(err)
      return new Result(null, false)
    }
  }
  if (option == 'getCallDetails') {
    //查询今日回答记录全部信息
    if (!obj) {
      sql = `SELECT * FROM  callDetails WHERE day=${day} AND year=${year} ORDER BY callTime DESC `
    }
    else if (obj == 'all') {
      sql = `SELECT * FROM callDetails 
    WHERE class IN (
    SELECT DISTINCT class 
    FROM callDetails 
    WHERE year = ${year})
    AND year = ${year}
    ORDER BY class
    `
    }
    let res = await dbPromise('all', sql)
    console.log('查询回答记录', res)
    return new Result(res)
  }
  if (option == 'insertCall') {
    try {
      sql = `INSERT INTO callDetails(name,class,num,callTime,answerResult,day,month,year)
             VALUES('${obj.name}','${obj.class}','${obj.num}','${obj.callTime}','${obj.answerResult}','${obj.day}','${obj.month}','${obj.year}')
            `
      await dbPromise('all', sql)
      return new Result(null)
    } catch (err) {
      console.log(err)
      return new Result(null, false)
    }
  }
  if (option == 'deleteCallById') {
    try {
      sql = `DELETE FROM callDetails WHERE id=${obj}`
      await dbPromise('all', sql)
      return new Result(null)
    }
    catch {
      return new Result(null, false)
    }

  }
  if (option == 'deleteAll') {
    try {
      sql = `DELETE FROM callDetails`
      await dbPromise('all', sql)
      sql = `DELETE FROM user`
      await dbPromise('all', sql)
      sql = `DELETE FROM fileData`
      await dbPromise('all', sql)
      return new Result(null)
    } catch {
      return new Result(null, false)

    }
  }

  // console.log(sql)
}
app.whenReady().then(async () => {
  // Set app user model id for windows
  electronApp.setAppUserModelId('com.electron')

  ipcMain.on('rendererSend', async (event, option, value) => {
    let res = await DataByUser(option, value)
    console.log('ress=>', res)
    event.reply('mainOut', res)
  })
  ipcMain.on('menuBar', (e, type) => {
    console.log(type)

    if (type == 1) {
      mini()
    }
    if (type == 2) {
      maximize()
    }
    if (type == 3) {
      close()
    }
    if (type == 4) {
      relaunch()
    }
  })
  // Default open or close DevTools by F12 in development
  // and ignore CommandOrControl + R in production.
  // see https://github.com/alex8088/electron-toolkit/tree/master/packages/utils
  app.on('browser-window-created', (_, window) => {
    optimizer.watchWindowShortcuts(window)
  })

  // IPC test
  ipcMain.on('ping', () => console.log('pong'))

  createWindow()

  app.on('activate', function () {
    // On macOS it's common to re-create a window in the app when the
    // dock icon is clicked and there are no other windows open.
    if (BrowserWindow.getAllWindows().length === 0) createWindow()
  })
})
const mini = () => {
  mainWindow.minimize()
}
const maximize = () => {
  if (mainWindow.isMaximized()) {
    mainWindow.unmaximize()
  } else {
    mainWindow.maximize()
  }
}
const close = () => {
  mainWindow.close()
}
const relaunch = () => {
  app.relaunch();
  app.quit();
}
// Quit when all windows are closed, except on macOS. There, it's common
// for applications and their menu bar to stay active until the user quits
// explicitly with Cmd + Q.
app.on('window-all-closed', () => {
  if (process.platform !== 'darwin') {
    app.quit()
  }
})

// In this file you can include the rest of your app"s specific main process
// code. You can also put them in separate files and require them here.
